use [MVPNorthwind]
GO

PRINT 'Creating View - CustomersAndSuppliersByCity'
GO
create view CustomerAndSuppliersByCity AS
SELECT City, CompanyName, ContactName, 'Customers' AS Relationship 
FROM Customers
UNION SELECT City, CompanyName, ContactName, 'Suppliers'
FROM Suppliers
--ORDER BY City, CompanyName
GO

PRINT 'Creating View - AlphabeticalListOfProducts'
GO
create view AlphabeticalListOfProduct AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))
GO

PRINT 'Creating View - CurrentProductList'
GO
create view CurrentProductList AS
SELECT Product_List.ProductID, Product_List.ProductName
FROM Products AS Product_List
WHERE (((Product_List.Discontinued)=0))
--ORDER BY Product_List.ProductName
GO

PRINT 'Creating View - OrdersQry'
GO
create view OrdersQry AS
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, 
	Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, 
	Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, 
	Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GO

PRINT 'Creating View - ProductsAboveAveragePrice'
GO
create view ProductsAboveAveragePrice AS
SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE Products.UnitPrice>(SELECT AVG(UnitPrice) From Products)
--ORDER BY Products.UnitPrice DESC
GO

PRINT 'Creating View - ProductsByCategory'
GO
create view ProductsByCategory AS
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued <> 1
--ORDER BY Categories.CategoryName, Products.ProductName
GO

PRINT 'Creating View - QuarterlyOrders'
GO
create view QuarterlyOrders AS
SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
GO

PRINT 'Creating View - INVOICES'
GO
create view Invoices AS
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, 
	Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, 
	Customers.Region, Customers.PostalCode, Customers.Country, 
	(FirstName + ' ' + LastName) AS Salesperson, 
	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, 
	OrderDetails.ProductID, Products.ProductName, OrderDetails.UnitPrice, OrderDetails.Quantity, 
	OrderDetails.Discount, 
	(CONVERT(money,(OrderDetails.UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM 	Shippers INNER JOIN 
		(Products INNER JOIN 
			(
				(Employees INNER JOIN 
					(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
				ON Employees.EmployeeID = Orders.EmployeeID) 
			INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID) 
		ON Products.ProductID = OrderDetails.ProductID) 
	ON Shippers.ShipperID = Orders.ShipVia
GO

PRINT 'Creating View - OrderDetailsExtended'
GO
create view OrderDetailsExtended AS
SELECT OrderDetails.OrderID, OrderDetails.ProductID, Products.ProductName, 
	OrderDetails.UnitPrice, OrderDetails.Quantity, OrderDetails.Discount, 
	(CONVERT(money,(OrderDetails.UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice
FROM Products INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
--ORDER BY OrderDetails.OrderID
GO

PRINT 'Creating View - OrderSubtotals'
GO
create view OrderSubtotals AS
SELECT OrderDetails.OrderID, Sum(CONVERT(money,(OrderDetails.UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal
FROM OrderDetails
GROUP BY OrderDetails.OrderID
GO

PRINT 'Creating View - ProductSalesFor1997'
GO
create view ProductSalesFor1997 AS
SELECT Categories.CategoryName, Products.ProductName, 
Sum(CONVERT(money,(OrderDetails.UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSales
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) 
	INNER JOIN (Orders 
		INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID) 
	ON Products.ProductID = OrderDetails.ProductID
WHERE (((Orders.ShippedDate) Between '19970101' And '19971231'))
GROUP BY Categories.CategoryName, Products.ProductName
GO

PRINT 'Creating View - CategorySalesFor1997'
GO
create view CategorySalesFor1997 AS
SELECT ProductSalesFor1997.CategoryName, Sum(ProductSalesFor1997.ProductSales) AS CategorySales
FROM ProductSalesFor1997
GROUP BY ProductSalesFor1997.CategoryName
GO

PRINT 'Creating View - SalesByCategory'
GO
create view SalesByCategory AS
SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, 
	Sum(OrderDetailsExtended.ExtendedPrice) AS ProductSales
FROM 	Categories INNER JOIN 
		(Products INNER JOIN 
			(Orders INNER JOIN OrderDetailsExtended ON Orders.OrderID = OrderDetailsExtended.OrderID) 
		ON Products.ProductID = OrderDetailsExtended.ProductID) 
	ON Categories.CategoryID = Products.CategoryID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName
--ORDER BY Products.ProductName
GO

PRINT 'Creating View - SalesTotalsByAmount'
GO
create view SalesTotalsByAmount AS
SELECT OrderSubtotals.Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate
FROM 	Customers INNER JOIN 
		(Orders INNER JOIN OrderSubtotals ON Orders.OrderID = OrderSubtotals.OrderID) 
	ON Customers.CustomerID = Orders.CustomerID
WHERE (OrderSubtotals.Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231')
GO

PRINT 'Creating View - SummaryOfSalesByQUarter'
GO
create view SummaryOfSalesByQuarter AS
SELECT Orders.ShippedDate, Orders.OrderID, OrderSubtotals.Subtotal
FROM Orders INNER JOIN OrderSubtotals ON Orders.OrderID = OrderSubtotals.OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate
GO

PRINT 'Creating View - SummaryOfSalesByYear'
GO
create view SummaryOfSalesByYear AS
SELECT Orders.ShippedDate, Orders.OrderID, OrderSubtotals.Subtotal
FROM Orders INNER JOIN OrderSubtotals ON Orders.OrderID = OrderSubtotals.OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate
GO